
import java.io.*;
import java.util.*;

import javax.xml.parsers.*;

import org.tmatesoft.sqljet.core.*;
import org.tmatesoft.sqljet.core.schema.*;
import org.tmatesoft.sqljet.core.table.*;
import org.w3c.dom.*;
import org.xml.sax.SAXException;

public class SqlConnection {

	public void SqlInit()
	{
		File dbFile=new File("SngStatsDb");
		SqlJetDb db=null;
		try {
			db=SqlJetDb.open(dbFile, true);
			db.beginTransaction(SqlJetTransactionMode.WRITE);
			db.createTable("Create table if not exists GameTypes (Room Varchar(30), Players Integer, Buyin Double, Rake Double)");
			db.createTable("Create table if not exists Stat (Name Varchar(15) UNIQUE,value varchar(9),Room varchar(20),Stake integer, Players integer)");
			db.createTable("Create table if not exists Rooms (Name Varchar(20) UNIQUE,path TEXT)");
			db.createIndex("Create index if not exists GameTypes_full_index on GameTypes (Room,Players,Buyin,Rake)");
			db.createIndex("Create index if not exists Stat_rooms_index on Stat (Name)");
			db.createIndex("Create index if not exists Stat_stakes_index on Stat (Name,Stake)");
			db.createIndex("Create index if not exists Stat_players_index on Stat (Name,Stake,Players)");
			if(!db.getSchema().getTableNames().contains("AvailableRooms")){
				db.createTable("Create table AvailableRooms (Name Varchar(20) UNIQUE, Network Varchar(20))");
				db.commit();
				db.beginTransaction(SqlJetTransactionMode.WRITE);
				ISqlJetTable table=db.getTable("AvailableRooms");
				InputStream f=Object.class.getResourceAsStream("/AvailableRooms.xml");
				
				DocumentBuilderFactory dbf=DocumentBuilderFactory.newInstance();
				DocumentBuilder dbb = null;
		        try {
		            dbb = dbf.newDocumentBuilder();
		        } catch (ParserConfigurationException pce) {
		            System.err.println(pce);
		            System.exit(1);
		        }
		        
		        Document doc = null;
		        try {
		            doc = dbb.parse(f);
		        } catch (SAXException se) {
		            System.err.println(se.getMessage());
		        } catch (IOException ioe) {
		            System.err.println(ioe);		            
		        }
		        NodeList nl=doc.getElementsByTagName("Room");
		        for(int i=0;i<nl.getLength();i++)
		        {
		        	table.insert(nl.item(i).getAttributes().item(0).getTextContent(),
		        			nl.item(i).getAttributes().item(1).getTextContent());
		        }
			}
			db.commit();
			db.close();
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
	}
	
	public void CreateRoomTable(String name)
	{
		File dbFile=new File("SngStatsDb");
		SqlJetDb db=null;
		try{
			db=SqlJetDb.open(dbFile, true);
			db.beginTransaction(SqlJetTransactionMode.WRITE);		
			db.createTable("Create table if not exists " +
					name+
					" (" +
					"date Integer, " +
					"edate Integer, " +
					"duration Integer, " +
					"gamenum long PRIMARY KEY, " +
					"players Integer, " +
					"finishing Integer, " +
					"buyin Double, " +
					"rake Double, " +
					"winnings Double)");
			
			db.commit();
			db.close();
			}
		catch(SqlJetException e) {
			e.printStackTrace();
		}
		
	}
	
	public void saveDatas(ArrayList<GameInfo> al)
	{
		File dbFile=new File("SngStatsDb");
		SqlJetDb db=null;
		try {
			db=SqlJetDb.open(dbFile, true);
			db.beginTransaction(SqlJetTransactionMode.WRITE);
			for(GameInfo g:al)
			{
				ISqlJetTable table=db.getTable(g.roomname);
				Map<String,Object> map=new HashMap<String, Object>();
				map.put("date", g.date.getTimeInMillis());
				map.put("edate", g.edate.getTimeInMillis());
				map.put("duration", g.duration.getTimeInMillis());
				map.put("gamenum", g.gamenum);
				map.put("players", g.players);
				map.put("finishing", g.finishing);
				map.put("buyin", g.buyin);
				map.put("rake", g.rake);
				map.put("winnings", g.winnings);
				table.insertByFieldNamesOr(SqlJetConflictAction.IGNORE,map);
				ISqlJetCursor cursor=db.getTable("GameTypes").lookup("GameTypes_full_index",g.roomname,g.players,g.buyin,g.rake);
				if(cursor.eof()==true)
					db.getTable("GameTypes").insertOr(SqlJetConflictAction.IGNORE,g.roomname,g.players,g.buyin,g.rake);//ha nincs hozza adom
			}
			db.commit();
			db.close();
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
	}

	public ArrayList<GameInfo> getDatas()
	{
		ArrayList<GameInfo> eredmeny=new ArrayList<GameInfo>();
		File file=new File("SngStatsDb");
		SqlJetDb db=new SqlJetDb(file, false);
		try {
			db.open();
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			Set<String> set=db.getSchema().getTableNames();
			for(String s:set)
			{
				ISqlJetTable table=db.getTable(s);
				ISqlJetCursor cursor=table.open();
				while(!cursor.eof())
				{
					GameInfo g=new GameInfo();
					g.buyin=cursor.getFloat("buyin");
					g.rake=cursor.getFloat("rake");
					g.winnings=cursor.getFloat("winnings");
					g.gamenum=cursor.getInteger("gamenum");
					g.players=Short.parseShort(cursor.getInteger("players")+"");
					g.finishing=Short.parseShort(cursor.getInteger("finishing")+"");
					g.date=Calendar.getInstance();
					g.date.setTimeInMillis(cursor.getInteger("date"));
					g.edate=Calendar.getInstance();
					g.edate.setTimeInMillis(cursor.getInteger("edate"));
					g.duration=Calendar.getInstance();
					g.duration.setTimeInMillis(cursor.getInteger("duration"));
					eredmeny.add(g);
					cursor.next();
				}
				cursor.close();
			}
			db.close();
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
		return eredmeny;
	}
	
	public void saveStat(Map<String,String> map)
	{
		File dbFile=new File("SngStatsDb");
		SqlJetDb db=null;
		try {
			db=SqlJetDb.open(dbFile,true);
			db.beginTransaction(SqlJetTransactionMode.WRITE);
			ISqlJetTable table=db.getTable("Stat");
			for(Map.Entry<String, String> entries:map.entrySet())
			{
				ISqlJetCursor cr=table.lookup("sqlite_autoindex_Stat_1",entries.getKey());
				if(!cr.eof()){
					cr.delete();
				}
				db.commit();
				db.beginTransaction(SqlJetTransactionMode.WRITE);
				table.insertOr(SqlJetConflictAction.REPLACE,entries.getKey(),entries.getValue());
			}
			db.commit();
			db.close();
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
	}

	public Map<String,String> getStat()
	{
		Map<String,String> map=new HashMap<String, String>();
		File dbFile=new File("SngStatsDb");
		SqlJetDb db=null;
		try {
			db=SqlJetDb.open(dbFile, false);
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			ISqlJetTable table=db.getTable("Stat");
			ISqlJetCursor cursor=table.open();
			while(!cursor.eof())
			{
				map.put(cursor.getString("name"), cursor.getString("value"));
				cursor.next();
			}
			cursor.close();
			db.close();
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
		return map;
	}
	
	public Map<String,String> getRooms()
	{
		Map<String,String> eredmeny=new HashMap<String, String>();
		File dbFile=new File("SngStatsDb");
		SqlJetDb db=null;
		try {
			db=SqlJetDb.open(dbFile, false);
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			ISqlJetTable table=db.getTable("Rooms");
			ISqlJetCursor cursor=table.open();
			while(!cursor.eof())
			{
				eredmeny.put(cursor.getString("name"), cursor.getString("path"));
				cursor.next();
			}
			cursor.close();
			db.close();
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
		return eredmeny;
	}
	
	public void saveRooms(Map<String,String> in)
	{
		File dbFile=new File("SngStatsDb");
		SqlJetDb db=null;
		try {
			db=SqlJetDb.open(dbFile,true);
			db.beginTransaction(SqlJetTransactionMode.WRITE);
			ISqlJetTable table=db.getTable("Rooms");
			for(Map.Entry<String, String> entries:in.entrySet())
			{
				table.insertOr(SqlJetConflictAction.IGNORE, entries.getKey(),entries.getValue());
			}
			db.commit();
			db.close();
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
	}
	
	public Set<String> getAvailableRooms(){
		Set<String> eredmeny=new HashSet<String>();
		File dbFile=new File("SngStatsDb");
		SqlJetDb db=null;
		try {
			db=SqlJetDb.open(dbFile, false);
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			ISqlJetTable table=db.getTable("AvailableRooms");
			ISqlJetCursor cursor=table.open();
			while(!cursor.eof())
			{
				eredmeny.add(cursor.getString("name"));
				cursor.next();
			}
			cursor.close();
			db.close();
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
		return eredmeny;
	}
	
	
}
